/**********************************************
** @Author: phy
** @Date:   2017-12-12 14:17:37
** @Last Modified by:   phy
***********************************************/
package models

import (
	"github.com/astaxie/beego/orm"
	//"WebAdminSys/libs"
	"errors"
	"reflect"
	"strconv"
	"fmt"
)




func RecordUserTradeData( page, pageSize int, strsql string) ([]orm.Params, int64,error) {
	tmpsql := "SELECT UI.USERID AS UID,UI.GAMEID AS GID,CAST(UI.NICKNAME AS char) AS UNAME, (UI.GOLD + UI.INSUREGOLD) AS ZiCHAN,UI.ALLWINLOSE AS ALLWINLOSE,"
 	tmpsql += "UI.TODAYWINLOSE AS TODAYWINLOSE,SUM(BuyScore) AS BuyScore,SUM(SellScore) AS SellScore,SUM(UGGSA.BuyScore - UGGSA.SellScore) AS SURPLUS,"
 	tmpsql += "SUM(BuyCount) AS BuyCount,SUM(SellCount) AS SellCount,FROM_UNIXTIME(UI.CHEATENDTIME) AS  CHEATENDTIME,UI.CHEATSCORE AS CHEATSCORE,"
 	tmpsql += "UI.CHEATRATE AS CHEATRATE,UI.CHEATLEFTSCORE AS CHEATLEFTSCORE,UI.`STATUS` AS USTATUS FROM UserGiveGiftSub AS UGGSA   INNER JOIN UserInfo AS UI ON UI.USERID = UGGSA.UserID  WHERE 1=1 "

	offset := (page - 1) * pageSize
	tmpsql = fmt.Sprintf(" %s %s GROUP BY UGGSA.UserID ORDER BY BuyScore, SURPLUS  DESC  LIMIT %d,%d; " ,tmpsql,strsql,offset,pageSize);
	var maps []orm.Params
	o := orm.NewOrm(); 
	_,err := o.Raw(tmpsql).Values(&maps);

	tmpsql2 := fmt.Sprintf("SELECT COUNT(*)  AS total  FROM UserGiveGiftSub AS UGGSA INNER JOIN UserInfo AS UI ON UI.USERID = UGGSA.UserID  WHERE 1=1 %s GROUP BY UGGSA.UserID",strsql);

	var maps2 []orm.Params  
	total,err := o.Raw(tmpsql2).Values(&maps2);
	if len(maps2) >= 1{
		strtmp :=  maps2[0]["total"].(string);
		total,_ = strconv.ParseInt(strtmp, 10, 64)  
	}

	return maps,total,err

}

func RecordUserTradeDataTitle( strsql string) ([]orm.Params,error){
	
	tmpsql := " SELECT SUM(UI.GOLD + UI.INSUREGOLD) AS ALLGOLD,SUM(UI.ALLWINLOSE) AS ALLALLWINLOSE,SUM(UI.TODAYWINLOSE) AS ALLTODAYWINLOSE,SUM(UI.ALLBUYSCORE) AS ALLALLBUYSCORE FROM UserGiveGiftSub AS UGGSA "
	tmpsql += " INNER JOIN UserInfo AS UI ON UI.USERID = UGGSA.UserID  WHERE 1=1  "

	tmpsql = fmt.Sprintf(" %s %s  GROUP BY UGGSA.UserID" ,tmpsql,strsql);
	var maps []orm.Params
	o := orm.NewOrm(); 
	_,err := o.Raw(tmpsql).Values(&maps);
	return maps,err

}

func UserRecordNewProtectData(page, pageSize int, strsql,StrUserKind string) ([]orm.Params, int64,error) {
	offset := (page - 1) * pageSize
	o := orm.NewOrm(); 
	var maps []orm.Params  
 
	tmpsql := "SELECT UI.GAMEID AS UGID,cast(UI.NICKNAME as char) AS UNICKNAME,UI.LASTLOGINTIME AS LASTLOGINTIME,UI.FIRSTBUYGOLDTIME AS FIRSTBUYGOLDTIME,";
	tmpsql += "UI.GOLD+UI.INSUREGOLD AS zichan,UI.ALLWINLOSE AS ALLWINLOSE,UI.TODAYWINLOSE AS TODAYWINLOSE,UI.ALLBUYSCORE AS ALLBUYSCORE,UI.ALLSELLSCORE AS ALLSELLSCORE,";
	tmpsql += "FROM_UNIXTIME(UI.CHEATENDTIME) AS CHEATENDTIME,UI.CHEATSCORE AS CHEATSCORE,UI.CHEATRATE AS CHEATRATE,UI.CHEATLEFTSCORE AS CHEATLEFTSCORE,";
	tmpsql += "UOI.ROOMID AS UOROOMID FROM UserInfo  AS UI LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID WHERE 1=1";

	
	if strsql != ""{
		tmpsql = fmt.Sprintf("%s  %s ",tmpsql,strsql);
	}
	if StrUserKind != ""{
		tmpsql = fmt.Sprintf("%s AND (%s) ",tmpsql,StrUserKind);
	}

	tmpsql += " ORDER BY zichan,ALLWINLOSE DESC"

	tmpsql = fmt.Sprintf(" %s LIMIT %d,%d;",tmpsql,offset,pageSize);
	tmpSql2 := "SELECT COUNT(*) AS total  FROM UserInfo  AS UI LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID WHERE 1=1 ";
	tmpSql2 = fmt.Sprintf("%s %s",tmpSql2,strsql);
	if StrUserKind != ""{
		tmpSql2 = fmt.Sprintf("%s AND (%s) ",tmpSql2,StrUserKind);
	}

	_,err := o.Raw(tmpsql).Values(&maps);
	var maps2 []orm.Params  
	total,err := o.Raw(tmpSql2).Values(&maps2);
	if len(maps2) >= 1{
		strtmp :=  maps2[0]["total"].(string);
		total,_ = strconv.ParseInt(strtmp, 10, 64)  
	}
	//fmt.Println("===========222222222",maps2, "maps =" ,len(maps2));
	//fmt.Println("===========",maps, "maps =" ,len(maps));
	return maps, total,err
}

func UserRecordNewProtectDataTitle( strsql,StrUserKind string) ([]orm.Params,error){
	
	tmpsql := " SELECT SUM(UI.GOLD + UI.INSUREGOLD) AS ALLGOLD FROM UserInfo  AS UI LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID WHERE 1=1";

	tmpsql = fmt.Sprintf(" %s  %s  " ,tmpsql,strsql);
	if StrUserKind != ""{
		tmpsql = fmt.Sprintf("%s AND (%s) ",tmpsql,StrUserKind);
	}
	var maps []orm.Params
	o := orm.NewOrm(); 
	_,err := o.Raw(tmpsql).Values(&maps);
	return maps,err

}

func UserNewKind(LastBuyTime,ALLBUYSCORE,ALLWINLOSE,LASTLOGINTIME,ALLSELLSCORE interface{},iNowTime int64) int {
	if reflect.TypeOf(LastBuyTime) == nil || reflect.TypeOf(ALLBUYSCORE)== nil || reflect.TypeOf(ALLWINLOSE)== nil || reflect.TypeOf(LASTLOGINTIME)== nil  || reflect.TypeOf(ALLSELLSCORE)== nil{
		return 0;
	}
	I64LastBuyTime,_ := strconv.ParseInt(LastBuyTime.(string), 10, 64)
	I64ALLBUYSCORE ,_ := strconv.ParseInt(ALLBUYSCORE.(string), 10, 64)
	I64ALLWINLOSE,_ := strconv.ParseInt(ALLWINLOSE.(string), 10, 64)
	I64LASTLOGINTIME,_ := strconv.ParseInt(LASTLOGINTIME.(string), 10, 64)
	I64ALLSELLSCORE,_ := strconv.ParseInt(ALLSELLSCORE.(string), 10, 64)
	if I64LastBuyTime > (iNowTime - 3*24*3600) && I64ALLBUYSCORE > 140000 && I64ALLBUYSCORE < 20000000 && I64ALLWINLOSE < -50000 &&  I64ALLWINLOSE >= -10000000{
		return 1;
	} else if I64LastBuyTime > (iNowTime - 7*24*3600) && I64ALLBUYSCORE > 20000000 && I64ALLBUYSCORE < 50000000 && I64ALLWINLOSE < -10000000 &&  I64ALLWINLOSE >= -25000000{
		return 2;
	} else if I64LastBuyTime > (iNowTime - 10*24*3600) &&  I64ALLBUYSCORE > 50000000 &&  I64ALLWINLOSE < -25000000{
		return 3;
	} else if I64LASTLOGINTIME > (iNowTime - 10*24*3600) && I64ALLSELLSCORE < 1{
		return 4
	}
	return 0;
}

func UserRecordCareData(page, pageSize int, strsql string) ([]orm.Params, int64,error) {
	offset := (page - 1) * pageSize
	o := orm.NewOrm(); 
	var maps []orm.Params  
 
	tmpsql := "SELECT UI.GAMEID AS UGID,cast(UI.NICKNAME as char) AS UNICKNAME,";
	tmpsql += "UI.GOLD+UI.INSUREGOLD AS zichan,UI.ALLWINLOSE AS ALLWINLOSE,UI.TODAYWINLOSE AS TODAYWINLOSE,UI.ALLBUYSCORE AS ALLBUYSCORE,UI.ALLSELLSCORE AS ALLSELLSCORE,";
	tmpsql += "UI.CHEATSCORE AS CHEATSCORE,UI.CHEATRATE AS CHEATRATE,UI.CHEATLEFTSCORE AS CHEATLEFTSCORE,UOI.ROOMID AS UOROOMID "
	tmpsql += "FROM UserInfo  AS UI LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID WHERE 1=1";

	
	if strsql != ""{
		tmpsql = fmt.Sprintf("%s  %s ",tmpsql,strsql);
	}
	
	tmpsql += " ORDER BY zichan,ALLWINLOSE DESC"

	tmpsql = fmt.Sprintf(" %s LIMIT %d,%d;",tmpsql,offset,pageSize);
	tmpSql2 := "SELECT COUNT(*) AS total  FROM UserInfo  AS UI LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID WHERE 1=1 ";
	tmpSql2 = fmt.Sprintf("%s %s",tmpSql2,strsql);


	_,err := o.Raw(tmpsql).Values(&maps);
	var maps2 []orm.Params  
	total,err := o.Raw(tmpSql2).Values(&maps2);
	if len(maps2) >= 1{
		strtmp :=  maps2[0]["total"].(string);
		total,_ = strconv.ParseInt(strtmp, 10, 64)  
	}
	//fmt.Println("===========222222222",maps2, "maps =" ,len(maps2));
	//fmt.Println("===========",maps, "maps =" ,len(maps));
	return maps, total,err
}

func UserRecordCareDataTitle( strsql string) ([]orm.Params,error){
	
	tmpsql := " SELECT SUM(UI.GOLD + UI.INSUREGOLD) AS ALLGOLD FROM UserInfo  AS UI LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID WHERE 1=1";

	tmpsql = fmt.Sprintf(" %s  %s  " ,tmpsql,strsql);

	var maps []orm.Params
	o := orm.NewOrm(); 
	_,err := o.Raw(tmpsql).Values(&maps);
	return maps,err

}

func UserRecordOnLineData(page, pageSize int, strsql string) ([]orm.Params, int64,error) {
	offset := (page - 1) * pageSize
	o := orm.NewOrm(); 
	var maps []orm.Params  
 
	tmpsql := "SELECT UI.GAMEID AS UGID,cast(UI.NICKNAME as char) AS UNAME,FROM_UNIXTIME(REGISTERTIME) AS URTIME,FROM_UNIXTIME(LASTLOGINTIME) AS ULLTIME,";
	tmpsql += "GOLD+INSUREGOLD AS zichan,ALLWINLOSE AS UALLWINLOSE,TODAYWINLOSE AS UTODAYW,ALLBUYSCORE AS UALL,";
	tmpsql += "UI.ALLSELLSCORE AS ALLSELLSCORE,FROM_UNIXTIME(CHEATENDTIME) AS UCHEAT,UI.CHEATSCORE AS CHEATSCORE,UI.CHEATRATE AS CHEATRATE,UI.CHEATLEFTSCORE AS CHEATLEFTSCORE,";
	tmpsql += "IFNULL(UP.userczje,0) AS USERALLC,UOI.ROOMID AS UOROOMID FROM UserInfo  AS UI ";
	tmpsql += "LEFT JOIN (SELECT IFNULL(SUM(RMB),0) AS userczje,GAMEID FROM  UserPurchaseInfo GROUP BY GAMEID) AS UP ON UI.GAMEID=UP.GAMEID ";
	tmpsql += "LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID WHERE UOI.ROOMID >=0 ";
	
	if strsql != ""{
		tmpsql = fmt.Sprintf("%s  %s",tmpsql,strsql);
	}
	

	tmpsql += " ORDER BY zichan DESC,ALLWINLOSE DESC"

	tmpsql = fmt.Sprintf(" %s LIMIT %d,%d;",tmpsql,offset,pageSize);
	tmpSql2 := "SELECT COUNT(*) AS total  FROM UserInfo  AS UI  LEFT JOIN (SELECT IFNULL(SUM(RMB),0) AS userczje,GAMEID FROM  UserPurchaseInfo GROUP BY GAMEID) AS UP ON UI.GAMEID=UP.GAMEID "
	tmpSql2 += "LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID WHERE UOI.ROOMID >=0 ";

	tmpSql2 = fmt.Sprintf("%s  %s",tmpSql2,strsql);
	 
	_,err := o.Raw(tmpsql).Values(&maps);
	var maps2 []orm.Params  
	total,err := o.Raw(tmpSql2).Values(&maps2);
	if len(maps2) >= 1{
		strtmp :=  maps2[0]["total"].(string);
		total,_ = strconv.ParseInt(strtmp, 10, 64)  
	}
	//fmt.Println("===========222222222",maps2, "maps =" ,len(maps2));
	//fmt.Println("===========",maps, "maps =" ,len(maps));
	return maps, total,err
}

func UserRecordOnLineDataTitle( strsql string) ([]orm.Params,error){
	
	o := orm.NewOrm(); 
	var maps []orm.Params  
 
	tmpsql := "SELECT IFNULL(SUM(GOLD+INSUREGOLD),0) AS ALLGOLD,IFNULL(SUM(ALLWINLOSE),0) AS AAWL,IFNULL(SUM(TODAYWINLOSE),0) AS ATWL,IFNULL(SUM(UP.userczje),0) AS ACZ ";
	tmpsql += " FROM UserInfo  AS UI ";
	tmpsql += "LEFT JOIN (SELECT IFNULL(SUM(RMB),0) AS userczje,GAMEID FROM  UserPurchaseInfo GROUP BY GAMEID) AS UP ON UI.GAMEID=UP.GAMEID ";
	tmpsql += "LEFT JOIN (SELECT IFNULL(ROOMID,-1) AS ROOMID,USERID FROM  UserOnlineInfo GROUP BY USERID) AS UOI ON UOI.USERID=UI.USERID  WHERE UOI.ROOMID >=0";

	tmpsql = fmt.Sprintf("%s %s",tmpsql,strsql);
	_,err := o.Raw(tmpsql).Values(&maps);

	return maps, err

}

func UserRecordWinLoseData( iAllRecord int, strsql, strsql2 string,userTableNames ...interface{}) ([]orm.Params, int64,error) {


	var maps []orm.Params
	iLenGth := len(userTableNames);
	if iLenGth < 1 {
		return maps,0,errors.New("表名错误！")
	}
	//fmt.Println("===========kkkkk",iLenGth);
	
	commonsqlstr := ""
	commonsqlstr = fmt.Sprintf(" SELECT * FROM %s WHERE 1=1 %s ",userTableNames[0].(string),strsql);
	if iLenGth > 1 {
		for k := 1; k < iLenGth; k ++ {
			commonsqlstr = fmt.Sprintf("%s UNION ALL SELECT * FROM %s WHERE 1=1 %s ",commonsqlstr,userTableNames[k].(string),strsql);
		}
	}

	o := orm.NewOrm(); 
	o.Using("dbgoldlog")

	
	tmpsql := "SELECT * FROM( SELECT UAT.UserID AS UID,UAT.GameID AS GID,SUM(UAT.ChangeGold) AS ChangeGold FROM "
	tmpsql = fmt.Sprintf("%s ( %s ) AS UAT  GROUP BY USERID )  AS ALLD WHERE 1=1 %s " ,tmpsql,commonsqlstr,strsql2);

	tmpsql = fmt.Sprintf(" %s ORDER BY ALLD.ChangeGold DESC LIMIT %d,%d;",tmpsql,0,iAllRecord);
	total,err := o.Raw(tmpsql).Values(&maps);

	return maps,total,err
}